package com.lanyou.esb.repository;

import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import com.lanyou.esb.entity.Http;

public interface QuantityControlRepository extends JpaRepository<Http, String> {
	@Query(nativeQuery = true, value = "SELECT f.FIELD_NAME,s.SYSTEM_NAME,h.IF_NAME,h.IF_STATUS,q.IF_REQUEST_QTY_ID,q.REQUEST_THRESHOLD,q.MAX_REQUEST_QTY FROM t_esb_bu_if_http h  LEFT JOIN t_cook_bu_field_system s ON h.FIELD_SYSTEM_ID=s.FIELD_SYSTEM_ID "
			+ "LEFT JOIN t_cook_db_field f ON s.FIELD_ID=f.FIELD_ID "
			+ "LEFT JOIN t_esb_bu_if_request_qty q ON h.IF_HTTP_ID=q.IF_ID AND q.REQUEST_UNIT=:reqUnit "
			+ "WHERE h.IS_ENABLE='1' "
			+ "AND (''=:ifName OR h.IF_NAME LIKE :ifName) "
			+ "AND (''=:systemId OR h.FIELD_SYSTEM_ID=:systemId) "
			+ "AND (''=:fieldId OR f.FIELD_ID=:fieldId) "
			+ "AND (''=:userId OR EXISTS(SELECT U0.FIELD_SYSTEM_ID FROM t_cook_bu_user_system U0 WHERE s.FIELD_SYSTEM_ID=U0.FIELD_SYSTEM_ID AND U0.USER_ID=:userId)) "
			+ "LIMIT :pageNumber,:pageSize")
	List<Object[]> findRequestQuantity(@Param("userId") String userId,
			@Param("ifName") String ifName, @Param("systemId") String systemId,
			@Param("fieldId") String fieldId, @Param("reqUnit") String reqUnit,
			@Param("pageNumber") long pageNumber,
			@Param("pageSize") long pageSize);

	@Query(nativeQuery = true, value = "SELECT count(h.IF_HTTP_ID) FROM t_esb_bu_if_http h  LEFT JOIN t_cook_bu_field_system s ON h.FIELD_SYSTEM_ID=s.FIELD_SYSTEM_ID "
			+ "LEFT JOIN t_cook_db_field f ON s.FIELD_ID=f.FIELD_ID "
			+ "WHERE h.IS_ENABLE='1' "
			+ "AND (''=:ifName OR h.IF_NAME LIKE :ifName) "
			+ "AND (''=:systemId OR h.FIELD_SYSTEM_ID=:systemId) "
			+ "AND (''=:fieldId OR f.FIELD_ID=:fieldId) "
			+ "AND (''=:userId OR EXISTS(SELECT U0.FIELD_SYSTEM_ID FROM t_cook_bu_user_system U0 WHERE s.FIELD_SYSTEM_ID=U0.FIELD_SYSTEM_ID AND U0.USER_ID=:userId)) "
			+ "")
	List<Object> findRequestQuantityTotalNum(@Param("userId") String userId,
			@Param("ifName") String ifName, @Param("systemId") String systemId,
			@Param("fieldId") String fieldId);

	@Query(nativeQuery = true, value = "SELECT V2.fieldId AS fieldId, "
			+ "(SELECT f.field_name FROM t_cook_db_field f WHERE f.field_id=V2.fieldId) AS fieldName, "
			+ "GROUP_CONCAT(V2.systemIds) AS fsIds, "
			+ "GROUP_CONCAT(V2.fsNames) AS fsNames, "
			+ "GROUP_CONCAT(V2.ifIds) AS ifIds, "
			+ "GROUP_CONCAT(V2.ifNames) AS ifNames, "
			+ "GROUP_CONCAT(V2.inEsbDates) AS inEsbDates, "
			+ "GROUP_CONCAT(V2.dayMaxTime) AS dayMaxTime, "
			+ "GROUP_CONCAT(V2.dayAvgTime) AS dayAvgTime, "
			+ "GROUP_CONCAT(V2.dayAccessTimes) AS dayAccessTimes, "
			+ "GROUP_CONCAT(V2.ifAmounts) AS ifAmounts, "
			+ "GROUP_CONCAT(V2.fsAmounts) AS fsAmounts "
			+ "FROM( "
			+ "	SELECT V1.fieldId AS fieldId, "
			+ "	GROUP_CONCAT(V1.systemId) AS systemIds, "
			+ "	GROUP_CONCAT(V1.fsName) AS fsNames, "
			+ "	GROUP_CONCAT(V1.ifIds) AS ifIds, "
			+ "	GROUP_CONCAT(V1.ifNames) AS ifNames, "
			+ "	GROUP_CONCAT(V1.inEsbDate) AS inEsbDates, "
			+ "	GROUP_CONCAT(V1.dayMaxTime) AS dayMaxTime, "
			+ "	GROUP_CONCAT(V1.dayAvgTime) AS dayAvgTime, "
			+ "	GROUP_CONCAT(V1.dayAccessTimes) AS dayAccessTimes, "
			+ "	GROUP_CONCAT(V1.ifAmounts) AS ifAmounts, "
			+ "	COUNT(V1.systemId) AS fsAmounts "
			+ "	FROM( "
			+ "		SELECT V0.fieldId AS fieldId, "
			+ "		V0.systemId AS systemId, "
			+ "		V0.fsName AS fsName, "
			+ "		(GROUP_CONCAT(V0.ifId)) AS ifIds, "
			+ "		(GROUP_CONCAT(V0.ifName)) AS ifNames, "
			+ "		(GROUP_CONCAT(V0.inEsbDate)) AS inEsbDate, "
			+ "		GROUP_CONCAT(V0.dayMaxTime) AS dayMaxTime, "
			+ "		GROUP_CONCAT(V0.dayAvgTime) AS dayAvgTime, "
			+ "		GROUP_CONCAT(V0.dayAccessTimes) AS dayAccessTimes, "
			+ "		COUNT(V0.ifId) AS ifAmounts "
			+ "		FROM( "
			+ "			SELECT s0.FIELD_ID AS fieldId, "
			+ "			s0.FIELD_SYSTEM_ID AS systemId, "
			+ "			s0.SYSTEM_NAME AS fsName, "
			+ "			m06.IF_ID AS ifId, "
			+ "			m06.IF_NAME AS ifName, "
			+ "			DATE_FORMAT(m06.IN_ESB_DATE,'%Y-%m-%d')	AS inEsbDate, "
			+ "			MAX(m06.TOTAL_RESPONSE_TIMES) dayMaxTime, "
			+ "			AVG(m06.TOTAL_RESPONSE_TIMES) dayAvgTime, "
			+ "			COUNT(DATE_FORMAT(m06.IN_ESB_DATE,'%Y-%m-%d')) dayAccessTimes "
			+ "			FROM t_esb_bu_if_http h0,t_cook_bu_field_system s0, t_esb_bu_access_log_m06 m06 "
			+ "			WHERE h0.IF_HTTP_ID=m06.IF_ID AND h0.FIELD_SYSTEM_ID=s0.FIELD_SYSTEM_ID "
			+ "			AND (''=:interfaceName OR m06.IF_NAME LIKE :interfaceName) "
			+ "			AND (''=:systemId OR s0.FIELD_SYSTEM_ID =:systemId) "
			+ "			AND (''=:fieldId OR s0.FIELD_ID=:fieldId) "
			+ "			AND ((''=:today OR DATE_FORMAT(m06.IN_ESB_DATE, '%Y-%m-%d %H:%i:%S') = DATE_FORMAT(:today, '%Y-%m-%d %H:%i:%S')) "
			+ "				OR(''=:yestoday OR DATE_FORMAT(m06.IN_ESB_DATE, '%Y-%m-%d %H:%i:%S') = DATE_FORMAT(:yestoday, '%Y-%m-%d %H:%i:%S') "
			+ "				OR((''=:today AND ''=:sevenBeforeDate) OR(m06.IN_ESB_DATE <= :today)  "
			+ "				AND m06.IN_ESB_DATE >= :sevenBeforeDate)))) "
			+ "			GROUP BY m06.IF_ID,inEsbDate " + "			ORDER BY inEsbDate "
			+ "		) AS V0 " + "	GROUP BY V0.systemId,V0.ifId) AS V1 "
			+ "GROUP BY V1.fieldId,V1.systemId) AS V2 "
			+ "GROUP BY V2.fieldId LIMIT :pageNumber,:pageSize")
	List<Object[]> findServiceResponseTimeInfo(
			@Param("interfaceName") String interfaceName,
			@Param("fieldId") String fieldId,
			@Param("systemId") String systemId, @Param("today") String today,
			@Param("yestoday") String yestoday,
			@Param("sevenBeforeDate") String sevenBeforeDate,
			@Param("pageNumber") long pageNumber,
			@Param("pageSize") long pageSize);

	@Query(nativeQuery = true, value = "SELECT COUNT(V3.fieldId) AS totalNum "
			+ "FROM "
			+ "(SELECT V2.fieldId AS fieldId "
			+ "FROM( "
			+ "	SELECT V1.fieldId AS fieldId "
			+ "	FROM( "
			+ "		SELECT V0.fieldId AS fieldId, "
			+ "		V0.systemId AS systemId "
			+ "		FROM( "
			+ "			SELECT s0.FIELD_ID AS fieldId, "
			+ "			s0.FIELD_SYSTEM_ID AS systemId, "
			+ "			m06.IF_ID AS ifId, "
			+ "			DATE_FORMAT(m06.IN_ESB_DATE,'%Y-%m-%d')	AS inEsbDate "
			+ "			FROM t_esb_bu_if_http h0,t_cook_bu_field_system s0, t_esb_bu_access_log_m06 m06 "
			+ "			WHERE h0.IF_HTTP_ID=m06.IF_ID AND h0.FIELD_SYSTEM_ID=s0.FIELD_SYSTEM_ID "
			+ "			AND (''=:interfaceName OR m06.IF_NAME LIKE :interfaceName) "
			+ "			AND (''=:systemId OR s0.FIELD_SYSTEM_ID=:systemId) "
			+ "			AND (''=:fieldId OR s0.FIELD_ID=:fieldId) "
			+ "			AND ((''=:today OR DATE_FORMAT(m06.IN_ESB_DATE, '%Y-%m-%d %H:%i:%S') = DATE_FORMAT(:today, '%Y-%m-%d %H:%i:%S')) "
			+ "				OR(''=:yestoday OR DATE_FORMAT(m06.IN_ESB_DATE, '%Y-%m-%d %H:%i:%S') = DATE_FORMAT(:yestoday, '%Y-%m-%d %H:%i:%S') "
			+ "				OR((''=:today AND ''=:sevenBeforeDate) OR(m06.IN_ESB_DATE >= :today)  "
			+ "				AND m06.IN_ESB_DATE <= :sevenBeforeDate)))) "
			+ "			GROUP BY m06.IF_ID,inEsbDate " + "			ORDER BY inEsbDate "
			+ "		) AS V0 " + "	GROUP BY V0.systemId,V0.ifId) AS V1 "
			+ "GROUP BY V1.fieldId,V1.systemId) AS V2 "
			+ "GROUP BY V2.fieldId) AS V3")
	List<Object> findServiceResponseTimeTotalNum(
			@Param("interfaceName") String interfaceName,
			@Param("fieldId") String fieldId,
			@Param("systemId") String systemId, @Param("today") String today,
			@Param("yestoday") String yestoday,
			@Param("sevenBeforeDate") String sevenBeforeDate);

	@Query(nativeQuery = true, value = "SELECT a0.IF_ALARM_ID as alarmId, "
			+ "a0.IF_ID as ifId, "
			+ "a0.IF_TYPE as ifType, "
			+ "a0.IF_NAME as ifName, "
			+ "(SELECT h.IS_ISOLATION FROM t_esb_bu_if_http h WHERE h.IF_HTTP_ID=a0.IF_ID) as isIsolation,"
			+ "a0.FIELD_SYSTEM_ID as systemId, "
			+ "a0.SYSTEM_CODE as systemCode, "
			+ "(SELECT s.SYSTEM_NAME FROM t_cook_bu_field_system s WHERE s.FIELD_SYSTEM_ID=a0.FIELD_SYSTEM_ID) AS systemName, "
			+ "(SELECT f.FIELD_ID FROM t_cook_bu_field_system s,t_cook_db_field f WHERE s.FIELD_SYSTEM_ID=a0.FIELD_SYSTEM_ID AND s.FIELD_ID=f.FIELD_ID) AS fieldId, "
			+ "(SELECT f.FIELD_NAME FROM t_cook_bu_field_system s,t_cook_db_field f WHERE s.FIELD_SYSTEM_ID=a0.FIELD_SYSTEM_ID AND s.FIELD_ID=f.FIELD_ID) AS fieldName, "
			+ "a0.IN_ESB_DATE as inEsbDate, "
			+ "a0.ALARM_LEVEL as alarmLevel, "
			+ "a0.ALARM_TYPE as alarmType, "
			+ "a0.ERROR_RECEIVER as errReceiver, "
			+ "a0.RESPONSE_CODE as resCode, "
			+ "a0.REQUEST_IP as reqIp, "
			+ "a0.REQUEST_METHOD as reqMethod, "
			+ "a0.REQUEST_URL as reqUrl, "
			+ "a0.REQUEST_MESSAGE as reqMess, "
			+ "a0.ALARM_PEAK_VALUE as peakValue, "
			+ "a0.ANSWER_TIMES as answerTimes, "
			+ "a0.ASSIGN_WORK_CODE as workCode, "
			+ "(SELECT w.ASSIGN_DATE FROM t_esb_bu_if_alarm_wo w WHERE w.ASSIGN_WORK_CODE=a0.ASSIGN_WORK_CODE) as assignDate, "
			+ "a0.FLOW as flow, "
			+ "a0.FLOW_THRESHOLD as flowThreshold,  "
			+ "a0.ERROR_DATE as errorDate  "
			+ "FROM t_esb_bu_if_alarm a0 "
			+ "WHERE 1=1 "
			+ "AND (''=:alarmType OR a0.ALARM_TYPE=:alarmType) "
			+ "AND (''=:systemId OR a0.FIELD_SYSTEM_ID=:systemId) "
			+ "AND (''=:fieldId OR EXISTS(SELECT s.FIELD_ID FROM t_cook_bu_field_system s WHERE s.FIELD_SYSTEM_ID=a0.FIELD_SYSTEM_ID AND s.FIELD_ID=:fieldId)) "
			+ "AND (''=:startDate OR a0.ERROR_DATE >= :startDate)  "
			+ "AND (''=:endDate OR a0.ERROR_DATE <= :endDate) "
			+ "AND (''=:isolation OR EXISTS(SELECT h.IF_HTTP_ID FROM t_esb_bu_if_http h WHERE h.IF_HTTP_ID=a0.IF_ID AND h.IS_ISOLATION=:isolation)) "
			+ "AND (''=:userId OR  EXISTS(SELECT u.USER_ID FROM t_cook_bu_user_system u WHERE u.FIELD_SYSTEM_ID=a0.FIELD_SYSTEM_ID AND u.USER_ID=:userId)) "
			+ "ORDER BY a0.CREATED_DATE  DESC  LIMIT :pageNumber,:pageSize")
	List<Object[]> findQuantityAlarmManagementInfo(
			@Param("userId") String userId, @Param("fieldId") String fieldId,
			@Param("systemId") String systemId,
			@Param("startDate") String startDate,
			@Param("endDate") String endDate,
			@Param("isolation") String isolation,
			@Param("alarmType") String alarmType,
			@Param("pageNumber") long pageNumber,
			@Param("pageSize") long pageSize);

	@Query(nativeQuery = true, value = "SELECT COUNT(a0.IF_ALARM_ID) totoalNum "
			+ "FROM t_esb_bu_if_alarm a0 "
			+ "WHERE 1=1 "
			+ "AND (''=:alarmType OR a0.ALARM_TYPE=:alarmType) "
			+ "AND (''=:systemId OR a0.FIELD_SYSTEM_ID=:systemId) "
			+ "AND (''=:fieldId OR EXISTS(SELECT s.FIELD_ID FROM t_cook_bu_field_system s WHERE s.FIELD_SYSTEM_ID=a0.FIELD_SYSTEM_ID AND s.FIELD_ID=:fieldId)) "
			+ "AND (''=:startDate OR a0.ERROR_DATE >= :startDate)  "
			+ "AND (''=:endDate OR a0.ERROR_DATE <= :endDate) "
			+ "AND (''=:isolation OR EXISTS(SELECT h.IF_HTTP_ID FROM t_esb_bu_if_http h WHERE h.IF_HTTP_ID=a0.IF_ID AND h.IS_ISOLATION=:isolation)) "
			+ "AND (''=:userId OR  EXISTS(SELECT u.USER_ID FROM t_cook_bu_user_system u WHERE u.FIELD_SYSTEM_ID=a0.FIELD_SYSTEM_ID AND u.USER_ID=:userId))")
	List<Object> findQuantityAlarmManagementTotalNum(
			@Param("userId") String userId, @Param("fieldId") String fieldId,
			@Param("systemId") String systemId,
			@Param("startDate") String startDate,
			@Param("endDate") String endDate,
			@Param("isolation") String isolation,
			@Param("alarmType") String alarmType);
}
